﻿#region Copyright (c) 2006-2012 nHydrate.org, All Rights Reserved
// --------------------------------------------------------------------- *
//                           NHYDRATE.ORG                                *
//              Copyright (c) 2006-2012 All Rights reserved              *
//                                                                       *
//                                                                       *
// This file and its contents are protected by United States and         *
// International copyright laws.  Unauthorized reproduction and/or       *
// distribution of all or any portion of the code contained herein       *
// is strictly prohibited and will result in severe civil and criminal   *
// penalties.  Any violations of this copyright will be prosecuted       *
// to the fullest extent possible under law.                             *
//                                                                       *
// THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
// TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
// TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
// CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
// THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF THE NHYDRATE GROUP *
//                                                                       *
// UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
// PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
// SUBSTANTIALLY THE SAME, FUNCTIONALITY AS THIS PRODUCT                 *
//                                                                       *
// THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
// CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF NHYDRATE,          *
// THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO             *
// INSURE ITS CONFIDENTIALITY.                                           *
//                                                                       *
// THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
// PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
// EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
// THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
// SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                       *
// THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
// --------------------------------------------------------------------- *
#endregion
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using nHydrate.Generator.Models;
using nHydrate.Generator.ProjectItemGenerators;
using System.Xml;
using nHydrate.Generator.Common.Util;
using nHydrate.Generator.MySQLInstaller.ProjectItemGenerators;
using nHydrate.Generator.Common.GeneratorFramework;

namespace nHydrate.Generator.MySQLInstaller
{
	internal static class SQLHelper
	{
		#region GetModelDifferenceSQL

		public static string GetModelDifferenceSQL(ModelRoot modelOld, ModelRoot modelNew)
		{
			var sb = new StringBuilder();

			#region Loop and Add tables

			foreach (var newT in modelNew.Database.Tables.Where(x => x.TypedTable != TypedTableConstants.EnumOnly).ToList())
			{
				var tList = modelOld.Database.Tables.GetByKey(newT.Key);
				if (tList.Length == 0)
				{
					//ADD TABLE
					sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLCreateTable(modelNew, newT));
					sb.AppendLine("GO");
					sb.AppendLine();
				}
			}

			#endregion

			#region Loop and Modify tables
			foreach (var newT in modelNew.Database.Tables.OrderBy(x => x.Name))
			{
				var oldT = modelOld.Database.Tables.GetByKey(newT.Key).FirstOrDefault();
				if (oldT != null)
				{
					var querylist = new List<string>();

					#region Rename table if need be
					if (oldT.DatabaseName != newT.DatabaseName)
					{
						sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameTable(oldT, newT));
						sb.AppendLine("GO");
					}
					#endregion

					#region Add columns
					foreach (var newC in newT.GetColumns())
					{
						var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
						if (oldC == null)
						{
							//ADD COLUMN
							sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLAddColumn(newC));
							sb.AppendLine("GO");
							sb.AppendLine();
						}
						//else if (newC.DatabaseName != oldC.DatabaseName)
						//{
						//  //RENAME COLUMN
						//  sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameColumn(oldC, newC));
						//  sb.AppendLine("GO");
						//  sb.AppendLine();
						//}

					}
					#endregion

					#region Modify Columns
					foreach (var newC in newT.GetColumns())
					{
						var oldC = Globals.GetColumnByKey(oldT.Columns, newC.Key);
						if (oldC != null)
						{
							var document = new XmlDocument();
							document.LoadXml("<a></a>");
							var n1 = XmlHelper.AddElement(document.DocumentElement, "q");
							var n2 = XmlHelper.AddElement(document.DocumentElement, "q");
							oldC.XmlAppend(n1);
							newC.XmlAppend(n2);
							if (newC.CorePropertiesHash != oldC.CorePropertiesHash)
							{
								//MODIFY COLUMN
								sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLModifyColumn(oldC, newC));
								sb.AppendLine("GO");
								sb.AppendLine();
							}
						}
					}
					#endregion

					#region Process Table Splits
					{
						var splits = modelNew.Refactorizations
							.Where(x => x is RefactorTableSplit)
							.Cast<RefactorTableSplit>()
							.Where(x => x.EntityKey1 == new Guid(newT.Key))
							.ToList();

						foreach (var split in splits)
						{
							var splitTable = modelNew.Database.Tables.FirstOrDefault(x => new Guid(x.Key) == split.EntityKey2);
							var origFields = oldT.GeneratedColumns.Where(x => split.ReMappedFieldIDList.Keys.Contains(new Guid(x.Key))).ToList();
							if (splitTable != null && origFields.Count > 0)
							{
								var newFields = new List<Column>();
								foreach (var item in origFields)
								{
									var newF = splitTable.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == split.ReMappedFieldIDList[new Guid(item.Key)]);
									if (newF != null)
										newFields.Add(newF);
								}

								newFields = newFields.Distinct().ToList();

								//If there are columns then process
								if (newFields.Count > 0)
								{
									sb.AppendLine("#PROCESS TABLE SPLIT [" + newT.DatabaseName + "] -> [" + splitTable.DatabaseName + "]");

									//Get the fields for generation
									//This may be a different number than original split since user can remove fields
									var genFields = new Dictionary<Column, Column>();
									foreach (var f in origFields)
									{
										//Get the new column from the new table as the name might have changed
										var newID = split.ReMappedFieldIDList[new Guid(f.Key)];
										var newF = splitTable.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == newID);
										if (newF != null)
										{
											genFields.Add(f, newF);
										}
									}

									//Process the actual script fields
									if (genFields.Count > 0)
									{
										//Turn on identity insert if necessary
										if (splitTable.PrimaryKeyColumns.Count(x => x.Identity == IdentityTypeConstants.Database) > 0)
											sb.AppendLine("SET identity_insert [" + splitTable.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(modelNew, splitTable) + "] on");

										sb.Append("INSERT INTO [" + splitTable.GetSQLSchema() + "].[" + splitTable.DatabaseName + "] (");
										foreach (var f in genFields.Keys)
										{
											//Get the new column from the new table as the name might have changed
											var newF = genFields[f];
											sb.Append("[" + newF.DatabaseName + "]");
											if (genFields.Keys.IndexOf(f) < genFields.Keys.Count - 1) sb.Append(", ");
										}
										sb.AppendLine(")");
										sb.Append("SELECT ");
										foreach (var f in genFields.Keys)
										{
											sb.Append("[" + f.DatabaseName + "]");
											if (genFields.Keys.IndexOf(f) < genFields.Keys.Count - 1) sb.Append(", ");
										}

										sb.AppendLine(" FROM [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "]");

										//Turn off identity insert if necessary
										if (splitTable.PrimaryKeyColumns.Count(x => x.Identity == IdentityTypeConstants.Database) > 0)
											sb.AppendLine("SET identity_insert [" + splitTable.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(modelNew, splitTable) + "] off");

										sb.AppendLine("GO");
										sb.AppendLine();
									}
								}
							}
						}
					} //Table Splits
					#endregion

					#region Process Table Combines
					{
						var splits = modelNew.Refactorizations
							.Where(x => x is RefactorTableCombine)
							.Cast<RefactorTableCombine>()
							.Where(x => x.EntityKey1 == new Guid(newT.Key))
							.ToList();

						foreach (var split in splits)
						{
							var deletedTable = modelOld.Database.Tables.FirstOrDefault(x => new Guid(x.Key) == split.EntityKey2);
							if (deletedTable != null)
							{
								var deletedOrigDeletedT = modelOld.Database.Tables.GetByKey(deletedTable.Key).FirstOrDefault();
								if (deletedOrigDeletedT != null)
								{
									var deletedFields = deletedOrigDeletedT.GeneratedColumns.Where(x => split.ReMappedFieldIDList.Keys.Contains(new Guid(x.Key))).ToList();
									if (deletedFields.Count > 0)
									{
										var targetFields = new List<Column>();
										foreach (var item in deletedFields)
										{
											var newF = newT.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == split.ReMappedFieldIDList[new Guid(item.Key)]);
											if (newF != null)
												targetFields.Add(newF);
										}

										targetFields = targetFields.Distinct().ToList();

										//If there are columns then process
										if (targetFields.Count > 0)
										{
											//Get the fields for generation
											//This may be a different number than original combine since user can remove fields
											var genFields = new Dictionary<Column, Column>();
											foreach (var f in deletedFields)
											{
												//Get the new column from the new table as the name might have changed
												var newID = split.ReMappedFieldIDList[new Guid(f.Key)];
												var newF = newT.GeneratedColumns.FirstOrDefault(x => new Guid(x.Key) == newID);
												if (newF != null)
												{
													genFields.Add(f, newF);
												}
											}

											//Process the actual script fields
											if (genFields.Count > 0)
											{
												sb.AppendLine("#PROCESS TABLE COMBINE [" + deletedTable.DatabaseName + "] into [" + newT.DatabaseName + "]");
												sb.Append("UPDATE [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] SET ");
												foreach (var f in genFields.Keys)
												{
													//Get the new column from the new table as the name might have changed
													var newF = genFields[f];
													sb.Append("[" + newF.DatabaseName + "] = [_deleted].[" + f.DatabaseName + "]");
													if (genFields.Keys.IndexOf(f) < genFields.Keys.Count - 1) sb.Append(", ");
												}
												sb.Append(" FROM [" + newT.GetSQLSchema() + "].[" + newT.DatabaseName + "] AS [_A] INNER JOIN ");
												sb.Append("[" + deletedTable.GetSQLSchema() + "].[" + deletedTable.DatabaseName + "] AS [_deleted] ON ");
												sb.Append("[_A].[" + newT.PrimaryKeyColumns.First().DatabaseName + "] = [_deleted].[" + deletedTable.PrimaryKeyColumns.First().DatabaseName + "]");
												sb.AppendLine();
												sb.AppendLine("GO");
												sb.AppendLine();
											}

										}
									}
								}
							}
						}
					} //Table Combine
					#endregion

					#region Delete Columns
					foreach (Reference oldRef in oldT.Columns)
					{
						var oldC = oldRef.Object as Column;
						var newC = Globals.GetColumnByKey(newT.Columns, oldC.Key);
						if (newC == null)
						{
							//DELETE COLUMN
							sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLDropColumn(modelNew, oldC));
							sb.AppendLine("GO");
							sb.AppendLine();
						}
						else if (newC.DatabaseName != oldC.DatabaseName)
						{
							////RENAME COLUMN
							//string sql = "if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + oldC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" +
							//             "AND not exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + newC.DatabaseName + "' and o.name = '" + newT.DatabaseName + "')" + Environment.NewLine +
							//             "EXEC sp_rename @objname = '" + newT.DatabaseName + "." + oldC.DatabaseName + "', @newname = '" + newC.DatabaseName + "', @objtype = 'COLUMN'";
							//if (!querylist.Contains(sql))
							//{
							//  querylist.Add(sql);
							//  sb.AppendLine(sql);
							//  sb.AppendLine("GO");
							//  sb.AppendLine();
							//}
						}

					}
					#endregion

					#region Primary Key Changed

					//If the primary key changed, then generate a commented script that marks where the user can manually intervene
					var newPKHash = string.Join("|", newT.PrimaryKeyColumns.OrderBy(x => x.Name).Select(x => x.Name.ToLower()).ToArray());
					var oldPKHash = string.Join("|", oldT.PrimaryKeyColumns.OrderBy(x => x.Name).Select(x => x.Name.ToLower()).ToArray());
					if (newPKHash != oldPKHash)
					{
						sb.AppendLine("#GENERATION NOTE **");
						sb.AppendLine("#THE PRIMARY KEY HAS CHANGED, THIS MAY REQUIRE MANUAL INTERVENTION");
						sb.AppendLine("#THE FOLLOWING SCRIPT WILL DROP AND READD THE PRIMARY KEY HOWEVER IF THERE ARE RELATIONSHIPS");
						sb.AppendLine("#BASED ON THIS IT, THE SCRIPT WILL FAIL. YOU MUST DROP ALL RELATIONSHIPS FIRST.");

						var tableName = Globals.GetTableDatabaseName(modelNew, newT);
						var indexName = "PK_" + tableName;
						indexName = indexName.ToUpper();
						sb.AppendLine("#DROP PRIMARY KEY FOR TABLE [" + tableName + "]");
						sb.AppendLine("#IF EXISTS (select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME = '" + indexName + "' AND table_schema = DATABASE()) THEN");
						sb.AppendLine("#ALTER TABLE [" + tableName + "] DROP CONSTRAINT [" + indexName + "]");
						sb.AppendLine("#GO");
						sb.AppendLine();

						var sql = Globals.GetPrimaryKeyCreateScript(newT, modelNew);
						var lines = sql.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);

						//Comment the whole SQL block
						var index = 0;
						foreach (var s in lines)
						{
							var l = s;
							if (!l.StartsWith("#"))
								l = "#" + l;
							lines[index] = l;
							index++;
						}

						sb.Append(string.Join("\r\n", lines));
					}

					#endregion

					#region Drop Foreign Keys
					foreach (var r1 in oldT.GetRelations().ToList())
					{
						var r2 = newT.GetRelations().FirstOrDefault(x => x.UniqueHash == r1.UniqueHash);
						if (r2 == null)
						{
							nHydrate.Core.SQLGeneration.MySQLEmit.PrependRunProc(sb);
							var indexName = nHydrate.Core.SQLGeneration.SQLEmit.CreateFKName(r1).ToUpper();
							var targetTable = r1.ChildTable;
							sb.AppendLine("#REMOVE FOREIGN KEY");
							sb.AppendLine("IF EXISTS (select * from information_schema.KEY_COLUMN_USAGE where CONSTRAINT_NAME = '" + indexName + "' AND table_schema = DATABASE()) THEN");
							sb.AppendLine("ALTER TABLE `" + targetTable.DatabaseName + "` DROP FOREIGN KEY `" + indexName + "`;");
							sb.AppendLine("END IF;");
							nHydrate.Core.SQLGeneration.MySQLEmit.PostpendRunProc(sb);
							sb.AppendLine();
						}
					}
					#endregion

					#region Rename audit columns if necessary
					if (modelOld.Database.CreatedByColumnName != modelNew.Database.CreatedByColumnName)
					{
						sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameColumn(newT.DatabaseName, modelOld.Database.CreatedByColumnName, new Column(modelNew.Root) { Name = modelNew.Database.CreatedByColumnName, AllowNull = true, DataType = System.Data.SqlDbType.VarChar, Length = 50 }));
						sb.AppendLine("GO");
					}
					if (modelOld.Database.CreatedDateColumnName != modelNew.Database.CreatedDateColumnName)
					{
						sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameColumn(newT.DatabaseName, modelOld.Database.CreatedDateColumnName, new Column(modelNew.Root) { Name = modelNew.Database.CreatedDateColumnName, AllowNull = true, DataType = System.Data.SqlDbType.DateTime }));
						sb.AppendLine("GO");
					}
					if (modelOld.Database.ModifiedByColumnName != modelNew.Database.ModifiedByColumnName)
					{
						sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameColumn(newT.DatabaseName, modelOld.Database.ModifiedByColumnName, new Column(modelNew.Root) { Name = modelNew.Database.ModifiedByColumnName, AllowNull = true, DataType = System.Data.SqlDbType.VarChar, Length = 50 }));
						sb.AppendLine("GO");
					}
					if (modelOld.Database.ModifiedDateColumnName != modelNew.Database.ModifiedDateColumnName)
					{
						sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameColumn(newT.DatabaseName, modelOld.Database.ModifiedDateColumnName, new Column(modelNew.Root) { Name = modelNew.Database.ModifiedDateColumnName, AllowNull = true, DataType = System.Data.SqlDbType.DateTime }));
						sb.AppendLine("GO");
					}
					if (modelOld.Database.TimestampColumnName != modelNew.Database.TimestampColumnName)
					{
						sb.AppendLine(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLRenameColumn(newT.DatabaseName, modelOld.Database.TimestampColumnName, new Column(modelNew.Root) { Name = modelNew.Database.TimestampColumnName, AllowNull = true, DataType = System.Data.SqlDbType.Timestamp }));
						sb.AppendLine("GO");
					}
					#endregion
				}
			}

			#endregion

			#region Loop and DELETE tables
			foreach (var oldT in modelOld.Database.Tables.Where(x => x.Generated))
			{
				var newT = modelNew.Database.Tables.Where(x => x.Generated && x.Key.ToLower() == oldT.Key.ToLower()).FirstOrDefault();
				if (newT == null)
				{
					//DELETE TABLE
					sb.Append(nHydrate.Core.SQLGeneration.MySQLEmit.GetSQLDropTable(oldT));
					sb.AppendLine("GO");
					sb.AppendLine();
				}
				else if (newT != null && oldT.AllowAuditTracking && !newT.AllowAuditTracking)
				{
					//If the old model had audit tracking and the new one does not, add a TODO in the script
					var tableName = "__AUDIT__" + Globals.GetTableDatabaseName(modelOld, oldT);
					sb.AppendLine("#TODO: REMOVE AUDIT TABLE '" + tableName + "'");
					sb.AppendLine("#The previous model had audit tracking turn on for table '" + Globals.GetTableDatabaseName(modelOld, oldT) + "' and now it is turned off.");
					sb.AppendLine("#The audit table will not be removed automatically. If you want to remove it, uncomment the following script.");
					sb.AppendLine("#DROP TABLE [" + tableName + "]");
					sb.AppendLine("#GO");
					sb.AppendLine();
				}
				//else if (tList[0].DatabaseName != oldT.DatabaseName)
				//{
				//  //RENAME TABLE
				//  sb.AppendLine("if exists(select * from sysobjects where name = '" + oldT.DatabaseName + "' and xtype = 'U')");
				//  sb.AppendLine("exec sp_rename [" + oldT.DatabaseName + "], [" + tList[0].DatabaseName + "]");
				//}
			}
			#endregion

			#region Move tables between schemas

			var reschema = 0;
			foreach (var newT in modelNew.Database.Tables.AsEnumerable())
			{
				var tList = modelOld.Database.Tables.GetByKey(newT.Key);
				if (tList.Length > 0)
				{
					var oldT = tList[0];
					if (string.Compare(oldT.GetSQLSchema(), newT.GetSQLSchema(), true) != 0)
					{
						if (reschema == 0)
							sb.AppendLine("#MOVE TABLES TO PROPER SCHEMA IF NEED BE");
							
							//This table has changed schema so script it
						sb.AppendLine("#CREATE DATABASE SCHEMAS");
						sb.AppendLine("if not exists(select * from sys.schemas where name = '" + newT.GetSQLSchema() + "')");
						sb.AppendLine("exec('CREATE SCHEMA [" + newT.GetSQLSchema() + "]')");
						sb.AppendLine("GO");
						sb.AppendLine("if exists (select * from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id where t.name = '" + newT.DatabaseName + "' and s.name = '" + oldT.GetSQLSchema() + "')");
						sb.AppendLine("	ALTER SCHEMA [" + newT.GetSQLSchema() + "] TRANSFER [" + oldT.GetSQLSchema() + "].[" + newT.DatabaseName + "];");
						sb.AppendLine("GO");
						reschema++;
					}
				}
			}

			if (reschema > 0) sb.AppendLine();

			#endregion

			return sb.ToString();
		}

		#endregion

		#region GetSQLSchema

		public static string GetSQLSchema(this Table item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		public static string GetSQLSchema(this TableComponent item)
		{
			if (string.IsNullOrEmpty(item.Parent.DBSchema)) return "dbo";
			return item.Parent.DBSchema;
		}

		public static string GetSQLSchema(this CustomStoredProcedure item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		public static string GetSQLSchema(this CustomView item)
		{
			if (string.IsNullOrEmpty(item.DBSchema)) return "dbo";
			return item.DBSchema;
		}

		#endregion

	}
}

